#数据处理模块
from importlib.util import source_hash
from msilib.schema import Error
import pandas as pd
import sqlLink

def createHeaderType(import_type,import_mode):
  '''
  import_type:引入类型(categroy 或 page) | import_mode:引入模式(file或sql)\n
  创建对应sql字段名称或限定类型
  '''
  if import_type == 'categroy':
    datatype = {'node_id':'int','parent':'int'}
    columns = ['node_id','name','parent']
    # 三元表达式
    res = {'file':datatype , 'sql': columns}[import_mode]
    return res
  elif import_type == 'page':
    datatype = {'page_id':'int','categroy_id':'int'}
    columns = ['page_id','page_name','page_link','categroy_id']
    res = {'file':datatype , 'sql': columns}[import_mode]
    return res

def loadInitialData(mode,import_type,database='',table='',file_path=''):
  '''
  mode:file/sql 数据读取模式(从本地csv或excel文件/数据库) | import_type:处理的目标数据(category分类或page页面) | path:excel表路径 | database:数据库 | table:表名称\n
  读取初始数据,返回DataFrame
  '''
  if mode=='file':
    #获取文件类型
    file_type = file_path.split('.')[-1].lower()
    if file_type == 'xlsx':
      datatype = createHeaderType(import_type,mode)
      data = pd.read_excel('{}'.format(file_path),dtype=datatype)
    elif file_type == 'csv':
      try:
        datatype = createHeaderType(import_type,mode)
        data = pd.read_csv('{}'.format(file_path),dtype=datatype)
      except UnicodeDecodeError:
        error_info = 'csv编码格式错误,请改为UTF8'
        return error_info
      except Exception as Error:
        error_info = Error
        return error_info
    print('获取到DataFrame数据',file_path)
  elif mode=='sql':
    sql = "SELECT * FROM `{}`.`{}`".format(database,table)
    data = sqlLink.sqlGetData(database,sql)
    datatype = createHeaderType(import_type,mode)
    data = pd.DataFrame(data,columns=datatype)
    print('获取到sql数据')
  #从1开始索引
  data.index = data.index +1
  print(data)
  return data

def rebuild_tree(lft,parent,source):
  '''
  father: 起始节点id, lft: 节点左值
  '''
  rgt = lft+1  
  level_nodes = source.query('parent=={}'.format(parent))['node_id']
  #获取子节点集合
  for i in level_nodes:
    # print(i,type(i))
    #计算子节点的左右值
    rgt = rebuild_tree(rgt,i,source)
  #表添加左右值
  source.loc[parent,'lft'] = lft
  source.loc[parent,'rgt'] = rgt
  return rgt + 1


if __name__ == "__main__":
  # data = pd.read_excel('初始数据.xlsx',dtype={'node_id':'int','parent':'int'})
  sql = "SELECT * FROM `{}`.`{}`".format('itsite','test_copy1')
  data = pd.DataFrame( sqlLink.sqlGetData('itsite',sql),columns=['node_id','name','lft','rgt','father'])
  data.index = data.index +1
  print(data)
  res = data.copy()
  rebuild_tree(1,1,res)
  print(res)